package com.link2room.rodimus.service.impl;

import com.link2room.common.dao.ICommonDao;
import com.link2room.common.jpa.result.QueryResult;
import com.link2room.common.util.ClassConverUtil;
import com.link2room.rodimus.dto.param.api.RankingListApiParamDto;
import com.link2room.rodimus.mapper.RankingListMapper;
import com.link2room.rodimus.dto.result.base.RankingListDto;
import com.link2room.rodimus.service.IRankingListService;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

/**
 * Created by VegetaKo on 2016/11/21.
 */
@Service("rankingListService")
public class RankingListServiceImpl implements IRankingListService {
    @Resource
    private ICommonDao commonDao;

    @Override
    public QueryResult<RankingListDto> getRankingList(RankingListApiParamDto rankingListApiParamDto) throws Exception {
           List objects=new ArrayList<>();
        QueryResult<RankingListDto> rankingListDtoQueryResult = new QueryResult<>();
        if(rankingListApiParamDto.getRankingListType().equals("MONTH") ){

            rankingListDtoQueryResult= ClassConverUtil.copyQueryResultToDtoQueryResult( getMonthRankingList(rankingListApiParamDto),RankingListDto.class,true);
        }
        else if( rankingListApiParamDto.getRankingListType().equals("WEEK")){

            rankingListDtoQueryResult=ClassConverUtil.copyQueryResultToDtoQueryResult( getWeekRankingList(rankingListApiParamDto),RankingListDto.class,true);

        }
        else{
            rankingListDtoQueryResult=ClassConverUtil.copyQueryResultToDtoQueryResult(getDayRankingList(rankingListApiParamDto),RankingListDto.class,true);
        }
        return rankingListDtoQueryResult;
    }

    private QueryResult<RankingListMapper> getDayRankingList(RankingListApiParamDto rankingListApiParamDto) throws Exception {
        List objects=new ArrayList<>();
        List countObjects=new ArrayList<>();
        String querySQL = this.makeDayRankingSQL(rankingListApiParamDto,objects);
        String countSQL = this.makeDayRankingCountSQL(rankingListApiParamDto,countObjects);
        QueryResult<RankingListMapper> rankingListDtoQueryResult=commonDao.findByNative(querySQL,countSQL,objects,countObjects, RankingListMapper.class, rankingListApiParamDto.getCurrentPage(), rankingListApiParamDto.getPageSize());
        return rankingListDtoQueryResult;
    }

    private QueryResult<RankingListMapper> getWeekRankingList(RankingListApiParamDto rankingListApiParamDto) throws Exception {
        List objects=new ArrayList<>();
        List countObjects=new ArrayList<>();
        String querySQL = this.makeWeekRankingSQL(rankingListApiParamDto,objects);
        String countSQL = this.makeWeekRankingCountSQL(rankingListApiParamDto,countObjects);
        QueryResult<RankingListMapper> rankingListDtoQueryResult=commonDao.findByNative(querySQL,countSQL,objects,countObjects, RankingListMapper.class, rankingListApiParamDto.getCurrentPage(), rankingListApiParamDto.getPageSize());
        return rankingListDtoQueryResult;
    }


    private QueryResult<RankingListMapper> getMonthRankingList(RankingListApiParamDto rankingListApiParamDto) throws Exception {
        List objects=new ArrayList<>();
        List countObjects=new ArrayList<>();
        String querySQL = this.makeMonthRankingSQL(rankingListApiParamDto,objects);
        String countSQL = this.makeMonthRankingCountSQL(rankingListApiParamDto,countObjects);
        QueryResult<RankingListMapper> rankingListDtoQueryResult=commonDao.findByNative(querySQL,countSQL,objects,countObjects, RankingListMapper.class, rankingListApiParamDto.getCurrentPage(), rankingListApiParamDto.getPageSize());
        return rankingListDtoQueryResult;
    }

    private String makeDayRankingSQL(RankingListApiParamDto rankingListApiParamDto, List objects)throws Exception{
        StringBuffer sb=new StringBuffer();
        sb.append("SELECT " +
                "  sr.lhotel_group_code lhotelGroupCode," +
                "  sr.lhotel_code lhotelCode," +
                "  sr.salesman_name salesmanName," +
                "  sum(sr.rewards) rewards" +
                "  FROM" +
                "  salesman_rankinglist sr " );
        sb.append("WHERE  sr.lhotel_group_code = ?1   ");
        objects.add(rankingListApiParamDto.getLhotelGroupCode());
        sb.append("AND sr.biz_datetime = ?2  ");
        objects.add(rankingListApiParamDto.getStartDatetime());
        //objects.add(DateUtil.format( DateUtil.addDays(new Date(),-1L),DateUtil.webFormat ));
        sb.append( " GROUP BY  sr.salesman_no" );
        sb.append( " ORDER BY  sum(sr.rewards) DESC" );
        return sb.toString();
    }

    private String makeDayRankingCountSQL(RankingListApiParamDto rankingListApiParamDto, List objects)throws Exception{
        StringBuffer sb=new StringBuffer();
        sb.append("SELECT count(1) FROM ( " );
        sb.append("SELECT " +
                "  1 " +
                "  FROM" +
                "  salesman_rankinglist sr " );
        sb.append("WHERE sr.lhotel_group_code = ?1  ");
        objects.add(rankingListApiParamDto.getLhotelGroupCode());
        sb.append("AND sr.biz_datetime = ?2  ");
        objects.add(rankingListApiParamDto.getStartDatetime());
        //objects.add(DateUtil.format( DateUtil.addDays(new Date(),-1L),DateUtil.webFormat ));
        sb.append( " GROUP BY  sr.salesman_no" );
        sb.append( " ORDER BY  sum(sr.rewards) DESC" );
        sb.append( " ) AS a" );
        return sb.toString();
    }

    private String makeWeekRankingSQL(RankingListApiParamDto rankingListApiParamDto, List objects)throws Exception{
        StringBuffer sb=new StringBuffer();
        sb.append("SELECT " +
                "  sr.lhotel_group_code lhotelGroupCode," +
                "  sr.lhotel_code lhotelCode," +
                "  sr.salesman_name salesmanName," +
                "  sum(sr.rewards) rewards" +
                "  FROM" +
                "  salesman_rankinglist sr " );
        sb.append("WHERE sr.lhotel_group_code = ?1  ");
        objects.add(rankingListApiParamDto.getLhotelGroupCode());
        sb.append("AND sr.week = ?2  ");
        Calendar cal = Calendar.getInstance();
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        cal.setTime(df.parse(rankingListApiParamDto.getStartDatetime()));
        objects.add( cal.get(Calendar.WEEK_OF_YEAR)-1);
        //objects.add(DateUtil.format( DateUtil.addDays(new Date(),-1L),DateUtil.webFormat ));
        sb.append( " GROUP BY  sr.salesman_no,sr.week" );
        sb.append( " ORDER BY  sum(sr.rewards) DESC" );
        return sb.toString();
    }

    private String makeWeekRankingCountSQL(RankingListApiParamDto rankingListApiParamDto, List objects)throws Exception{
        StringBuffer sb=new StringBuffer();
        sb.append("SELECT count(1) FROM ( " );
        sb.append("SELECT 1 " +
                "  FROM" +
                "  salesman_rankinglist sr " );
        sb.append("WHERE sr.lhotel_group_code = ?1  ");
        objects.add(rankingListApiParamDto.getLhotelGroupCode());
        sb.append("AND sr.week = ?2  ");
        Calendar cal = Calendar.getInstance();
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        cal.setTime(df.parse(rankingListApiParamDto.getStartDatetime()));
        objects.add( cal.get(Calendar.WEEK_OF_YEAR)-1);
        //objects.add(DateUtil.format( DateUtil.addDays(new Date(),-1L),DateUtil.webFormat ));
        sb.append( " GROUP BY  sr.salesman_no,sr.week" );
        sb.append( " ORDER BY  sum(sr.rewards) DESC" );
        sb.append( " ) AS a" );
        return sb.toString();
    }

    private String makeMonthRankingSQL(RankingListApiParamDto rankingListApiParamDto, List objects)throws Exception{
        StringBuffer sb=new StringBuffer();
        sb.append("SELECT " +
                "  sr.lhotel_group_code lhotelGroupCode," +
                "  sr.lhotel_code lhotelCode," +
                "  sr.salesman_name salesmanName," +
                "  sum(sr.rewards) rewards" +
                "  FROM" +
                "  salesman_rankinglist sr " );
        sb.append("WHERE sr.lhotel_group_code = ?1  ");
        objects.add(rankingListApiParamDto.getLhotelGroupCode());
        sb.append("AND sr.month = ?2  ");
        Calendar cal = Calendar.getInstance();
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        cal.setTime(df.parse(rankingListApiParamDto.getStartDatetime()));
        objects.add( cal.get(Calendar.MONTH));
        //objects.add(DateUtil.format( DateUtil.addDays(new Date(),-1L),DateUtil.webFormat ));

        sb.append( " GROUP BY  sr.salesman_no,sr.month" );
        sb.append( " ORDER BY  sum(sr.rewards) DESC" );
        return sb.toString();
    }

    private String makeMonthRankingCountSQL(RankingListApiParamDto rankingListApiParamDto, List objects)throws Exception{
        StringBuffer sb=new StringBuffer();
        sb.append("SELECT count(1) FROM ( " );
        sb.append("SELECT 1 " +
                "  FROM" +
                "  salesman_rankinglist sr " );
        sb.append("WHERE sr.lhotel_group_code = ?1  ");
        objects.add(rankingListApiParamDto.getLhotelGroupCode());
        sb.append("AND sr.month = ?2  ");
        Calendar cal = Calendar.getInstance();
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
        cal.setTime(df.parse(rankingListApiParamDto.getStartDatetime()));
        objects.add( cal.get(Calendar.MONTH));

        sb.append( " GROUP BY  sr.salesman_no,sr.month" );
        sb.append( " ORDER BY  sum(sr.rewards) DESC" );
        sb.append( " ) AS a" );
        return sb.toString();
    }


}
